package checkers;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * The <code>SaveAPI</code> class provides a simplified interface for saving
 * statistics and game states within the <code>Flying Moose Checkers Game</code>
 * . The class must be instantiated upon program start and properly closed upon
 * completion of the program to ensure data integrity.
 * 
 * Usage example:
 * <p>
 * <blockquote>
 * 
 * <pre>
 *     SaveAPI sapi = new SaveAPI();
 *     ...
 *     sapi.addStatistic("win", longGameLengthInMs);
 *     ...
 *     long totaltime = sapi.getStatistic("time");
 *     ...
 *     long[] allStats = sapi.getStatistics();
 *     ...
 *     sapi.saveGame(currentGame);
 *     ...
 *     Game game = sapi.loadGame();
 *     ...
 *     sapi.close();
 * </pre>
 * 
 * </blockquote>
 * </p>
 * 
 * @author Ilya
 * 
 */
public class SaveAPI {
	/**
	 * The database connection.
	 */
	private Connection conn;

	/**
	 * Default name for the database.
	 */
	private String dbName = "gamedata.db";

	/**
	 * Default path to the database, empty string means same directory.
	 */
	private String defaultDBPath = "";

	/**
	 * Cached maximum game ID in database.
	 */
	private int maxGameID;

	/**
	 * Cached maximum statistics ID in database.
	 */
	private int maxStatisticsID;

	private String fullPath;

	/**
	 * Initializes the newly created {@code SaveAPI} object so that it
	 * establishes a connection to the default database and initializes the
	 * cached variables.
	 */
	public SaveAPI() {
		this.connectDB();
	}

	/**
	 * Initializes the newly created {@code SaveAPI} object so that it
	 * establishes a connection to the specified database and initializes the
	 * cached variables.
	 * 
	 * @param dbName
	 *            Specifies the desired database name.
	 */
	public SaveAPI(String dbName) {
		this.dbName = dbName;
		this.connectDB(defaultDBPath, dbName);
	}

	/**
	 * Adds a statistic to the database, specifying win, loss, or draw, as well
	 * as the length of the game elapsed in milliseconds.
	 * 
	 * @param status
	 *            One of three options: 'win', 'loss', or 'draw'.
	 * @param time
	 *            Length of time in milliseconds.
	 * @param human
	 *            If the opponent is a human.
	 * @return Operation was successful.
	 */
	public boolean addStatistic(String status, long time, boolean human) {
		// id, win, loss, draw, time
		if (status == null) {
			return false;
		}
		time = (int) (time / 1000);
		connectDB();
		status = status.trim().toLowerCase();
		try {
			PreparedStatement prep = conn
					.prepareStatement("INSERT INTO statistics VALUES (?, ?, ?, ?, ?, ?);");

			prep.setInt(1, ++maxStatisticsID);

			prep.setInt(2, 0);
			prep.setInt(3, 0);
			prep.setInt(4, 0);

			if (status.equals("win")) {
				prep.setInt(2, 1);
			} else if (status.equals("loss")) {
				prep.setInt(3, 1);
			} else if (status.equals("draw")) {
				prep.setInt(4, 1);
			} else {
				System.err
						.println("Bad Statistic Parameter. Must be 'win','loss', or 'draw'.");
				return false;
			}

			prep.setLong(5, time);

			prep.setBoolean(6, human);

			prep.addBatch();

			conn.setAutoCommit(false);
			prep.executeBatch();
			conn.setAutoCommit(true);
			prep.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * Closes the connection to the database in a safe manner.
	 * 
	 * @return True if database was closed. False if already closed.
	 */
	public boolean close() {
		try {
			if (conn != null && !conn.isClosed()) {
				// conn.rollback();
				while (conn != null && !conn.isClosed()) {
					conn.close();
				}
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * Returns the lowest time in the database for human or AI statistics where
	 * there are wins.
	 * 
	 * @param human
	 *            If the opponent is a human.
	 * @return Lowest time in the database in seconds.
	 */
	public long getBestTime(boolean human) {
		connectDB();
		long bestTime = -1;
		try {
			bestTime = conn
					.createStatement()
					.executeQuery(
							"SELECT MIN(time) AS bestTime FROM statistics WHERE human="
									+ (human ? "1" : "0")
									+ " AND win = 1 ORDER BY time;").getLong(1);

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return bestTime > 0 ? bestTime : -1;

	}

	/**
	 * Return the requested statistic.
	 * 
	 * @param statistic
	 *            The statistic request: either 'win', 'loss', 'draw', or
	 *            'gamesplayed'.
	 * @param human
	 *            If the opponent is a human.
	 * @return The requested statistic value.
	 */
	public long getStatistic(String statistic, boolean human) {

		long[] statistics = getStatistics(human);
		statistic = statistic.toLowerCase();

		if (statistic.equals("win")) {
			return statistics[0];
		} else if (statistic.equals("loss")) {
			return statistics[1];
		} else if (statistic.equals("draw")) {
			return statistics[2];
		} else if (statistic.equals("gamesplayed")) {
			return statistics[4];
		} else {

			System.err
					.println("Bad Statistic Parameter. Must be 'win','loss','draw','time'");
		}

		return (Long) null;
	}

	/**
	 * Returns an array of all of the statistics, in order of {wins, losses,
	 * draws, gamesplayed}.
	 * 
	 * @param human
	 *            If the opponent is a human.
	 * @return long[] of statistics {wins, losses, draws, gamesplayed}.
	 */
	public long[] getStatistics(boolean human) {
		// wins, losses, draws, totaltime, gamesplayed
		connectDB();

		long[] statistics = new long[4];
		try {
			Statement stat = conn.createStatement();
			ResultSet rs;

			String query = "SELECT SUM(win) AS wins, "
					+ "SUM(loss) AS losses, " + "SUM(draw) AS draws, "
					+ "COUNT(1) AS gamesplayed " + " FROM statistics"
					+ " WHERE human =" + (human ? "1" : "0");

			rs = stat.executeQuery(query);

			statistics[0] = rs.getLong("wins");
			statistics[1] = rs.getLong("losses");
			statistics[2] = rs.getLong("draws");
			statistics[3] = rs.getLong("gamesplayed");
			rs.close();
			stat.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}

		return statistics;
	}

	/**
	 * Fetches and returns the most recent game entered into the database.
	 * 
	 * @return Returns the most recently saved game in the database. Null if no
	 *         games exist.
	 */
	public Object loadGame() {
		if (maxGameID < 0)
			return null;
		return loadGame(maxGameID);
	}

	/**
	 * Resets the database, deleting all of the data within it.
	 * 
	 * @return Operation was successful.
	 */
	public boolean reinitialize() {
		try {
			return initialize(true);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * Saves the game into the database.
	 * 
	 * @param game
	 *            The Object representing the game to save it.
	 * @return The Object was successfully saved.
	 */
	public boolean saveGame(Object game) {
		// id, game
		if (game == null) {
			return false;
		}
		connectDB();
		try {
			PreparedStatement prep = conn
					.prepareStatement("INSERT INTO games VALUES (?, ?);");

			prep.setInt(1, ++maxGameID);

			ByteArrayOutputStream baos = new ByteArrayOutputStream();

			new ObjectOutputStream(baos).writeObject(game);

			prep.setBytes(2, baos.toByteArray());

			prep.addBatch();

			conn.setAutoCommit(false);
			prep.executeBatch();
			conn.setAutoCommit(true);
			prep.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (IOException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * Updates the last statistic in the database, specifying win, loss, or
	 * draw, as well as the length of the game elapsed in milliseconds.
	 * 
	 * @param status
	 *            The new status you would like to assign to the last statistic.
	 * @param time
	 *            The new time in milliseconds.
	 * @param human
	 *            If the opponent is a human.
	 * @return The success of the update.
	 */
	public boolean updateLastStatistic(String status, long time, boolean human) {
		if (maxStatisticsID < 0 || status == null) {
			return false;
		}
		time = (int) (time / 1000);
		connectDB();
		status = status.trim().toLowerCase();
		try {
			PreparedStatement prep = conn
					.prepareStatement("UPDATE statistics SET win=?, loss=?, draw=?, time=?, human=? WHERE id=?;");

			prep.setInt(1, 0);
			prep.setInt(2, 0);
			prep.setInt(3, 0);

			if (status.equals("win")) {
				prep.setInt(1, 1);
			} else if (status.equals("loss")) {
				prep.setInt(2, 1);
			} else if (status.equals("draw")) {
				prep.setInt(3, 1);
			} else {
				System.err
						.println("Bad Statistic Parameter. Must be 'win','loss', or 'draw'.");
				return false;
			}

			prep.setLong(4, time);

			prep.setBoolean(5, human);

			prep.setInt(6, maxStatisticsID);

			prep.addBatch();

			conn.setAutoCommit(false);
			prep.executeBatch();
			conn.setAutoCommit(true);

			prep.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * Establishes a database connection using the default parameters.
	 * 
	 * @return The Connection.
	 */
	private Connection connectDB() {
		return connectDB(defaultDBPath + dbName);
	}

	/**
	 * Establishes a database connection to the database specified by fullPath.
	 * 
	 * @param fullPath
	 *            The full path to the database.
	 * @return The Connection.
	 */
	private Connection connectDB(String fullPath) {
		this.fullPath = fullPath;
		boolean isFresh = false;
		try {
			if ((conn == null) || conn.isClosed()) {
				if (!(new File(fullPath)).isFile()) {
					isFresh = true;
				}
				Class.forName("org.sqlite.JDBC");
				conn = DriverManager.getConnection("jdbc:sqlite:" + fullPath);

				initialize(isFresh);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return conn;

	}

	/**
	 * Establishes a database connection to the database specified by the path
	 * and name.
	 * 
	 * @param path
	 *            Path to the database.
	 * @param name
	 *            Name of the database.
	 * @return The Connection.
	 */
	private Connection connectDB(String path, String name) {
		return connectDB(path + name);
	}

	/**
	 * Initializes the database and class.
	 * 
	 * @param isFresh
	 *            Indicates whether to reset all data in the database.
	 * @return Operation was successful.
	 * @throws SQLException
	 */
	private boolean initialize(Boolean isFresh) throws SQLException {
		connectDB();
		Statement stat = conn.createStatement();
		ResultSet rs;

		if (isFresh) {

			try {
				stat.executeUpdate("DROP TABLE IF EXISTS statistics;");
				stat.executeUpdate("CREATE TABLE statistics (id INTEGER, win INTEGER, loss INTEGER, draw INTEGER, time NUMERIC, human NUMERIC);");

				stat.executeUpdate("DROP TABLE IF EXISTS games;");
				stat.executeUpdate("CREATE TABLE games (id INTEGER, game BLOB);");

				maxStatisticsID = -1;
				maxGameID = -1;
			} catch (SQLException e) {
				conn.close();
				File f = new File(fullPath);
				File f2 = new File(fullPath + "-journal");
				f.delete();
				f2.delete();
				connectDB();
				stat = conn.createStatement();

				stat.executeUpdate("DROP TABLE IF EXISTS statistics;");
				stat.executeUpdate("CREATE TABLE statistics (id INTEGER, win INTEGER, loss INTEGER, draw INTEGER, time NUMERIC, human NUMERIC);");

				stat.executeUpdate("DROP TABLE IF EXISTS games;");
				stat.executeUpdate("CREATE TABLE games (id INTEGER, game BLOB);");

				maxStatisticsID = -1;
				maxGameID = -1;
			}
		} else {
			rs = stat.executeQuery("SELECT MAX(id) AS id FROM statistics;");
			if (rs.next()) {
				maxStatisticsID = rs.getInt("id");
			} else {
				maxStatisticsID = -1;
			}
			rs.close();

			rs = stat.executeQuery("SELECT MAX(id) AS id FROM games;");
			if (rs.next()) {
				maxGameID = rs.getInt("id");
			} else {
				maxGameID = -1;
			}
			rs.close();

			stat.close();
		}

		return true;
	}

	/**
	 * Fetches and returns the saved game specified by id.
	 * 
	 * @param id
	 *            The id of the requested game.
	 * @return An Object representing the requested game.
	 */
	private Object loadGame(int id) {

		connectDB();

		Object game = null;

		Statement stat;
		ResultSet rs;
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery("SELECT game FROM games WHERE id = " + id
					+ ";");

			ByteArrayInputStream bais;
			ObjectInputStream ois;
			bais = new ByteArrayInputStream(rs.getBytes("game"));
			ois = new ObjectInputStream(bais);
			game = ois.readObject();
			rs.close();
			stat.close();

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return game;
	}
}
